﻿-- =============================================
-- Author:		朱旭
-- Create date: 2015-1-22 15:31:18
-- Description:	按客户行业统计成单数
-- =============================================
CREATE PROCEDURE [dbo].[Proc_Orders_Count_By_CompanyAndTime] 
	-- Add the parameters for the stored procedure here
	@CompanyID int, 
	@sTime DATETIME, 
	@eTime DATETIME 
AS
BEGIN
	SET NOCOUNT ON;
	;WITH list As( --查询符合条件的成单
		SELECT * FROM (
			SELECT o.id,o.StateId,o.CreateDate,o.CompanyID,o.[CusId]
			FROM [Order] o
			WHERE
			o.CreateDate >= @sTime AND o.CreateDate <= @eTime AND 
			o.CompanyID=@CompanyID AND
			o.StateId>1 AND o.StateId <>6
		) l
		
		LEFT JOIN (
			SELECT CustomerId,IndustryOneId,IndustryTwoId FROM Customer
		) c ON  
		l.CusId=c.CustomerId 
		
	),
	list2 AS ( --Group
		SELECT l.IndustryOneId,COUNT(l.id) AS [Count]
		FROM list l
		GROUP BY l.IndustryOneId
	)
	
	SELECT (SELECT Title FROM [Customer_Industry] WHERE id=l2.IndustryOneId) AS Title,* FROM list2 l2
END
